import os
import psycopg2
import psycopg2.extras
import sys
from shapely.wkt import dumps, loads
import constant

mod04_query="SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04"
myd04_query="SELECT filename, path, ST_AsText(corner) as corner FROM satorgmyd04"
mod07_query="SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmod07 order by aqstime asc"
myd07_query="SELECT filename, path,aqstime, ST_AsText(corner) as corner FROM satorgmyd07 order by aqstime asc"

insert_query="INSERT INTO {0}(aqstime, rasref3, filename, filepath, projection,type, collection) VALUES ('{1}'::timestamp, '{2}'::raster, '{3}', '{4}', 1, 1, 6)" 
update_query="UPDATE {0} SET rasref6='{1}'::raster,rasref10='{2}'::raster WHERE filename ='{3}'"
		
def insertMOD07(query):
	org_folder = "public/SatOrgMOD07"
	resample_folder= "apom/Resample/SatResampMOD07"
	temp3_format = "_T_3km.tif"
	press3_format = "_P_3km.tif"
	temp6_format = "_T_6km.tif"
	press6_format = "_P_6km.tif"
	temp10_format = "_T_10km.tif"
	press10_format = "_P_10km.tif"
	
	
	temp_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmod07temperature"
	press_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmod07pressure "
	
	
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			resamp_path = path.replace(org_folder,resample_folder)
			corner = loads(row["corner"])
			
			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				path = os.path.join(constant.folder_path, path)
				
				# resampled file path
				temp3_file = os.path.join(path.replace(org_folder, resample_folder), filename + temp3_format)
				temp6_file = os.path.join(path.replace(org_folder, resample_folder), filename + temp6_format)
				temp10_file = os.path.join(path.replace(org_folder, resample_folder), filename + temp10_format)
				
				press3_file = os.path.join(path.replace(org_folder, resample_folder), filename + press3_format)
				press6_file = os.path.join(path.replace(org_folder, resample_folder), filename + press6_format)
				press10_file = os.path.join(path.replace(org_folder, resample_folder), filename + press10_format)

				# apom.satresampmod04: table name
				# -F: tu dong insert ten file thanh 1 cot
				# raster2pgsql/{1}.sql: duong dan file sql
				if os.path.isfile(temp3_file):
					temp3_script=os.popen(temp_command.format("rasref3",temp3_file, filename)).read()
					first_index = temp3_script.find("('")
					last_index = temp3_script.find("':")
					temp3_ref=temp3_script[first_index+2:last_index]
				
					temp6_script=os.popen(temp_command.format("rasref6",temp6_file, filename)).read()
					first_index = temp6_script.find("('")
					last_index = temp6_script.find("':")
					temp6_ref=temp6_script[first_index+2:last_index]
					
					temp10_script=os.popen(temp_command.format("rasref10",temp10_file, filename)).read()
					first_index = temp10_script.find("('")
					last_index = temp10_script.find("':")
					temp10_ref=temp10_script[first_index+2:last_index]
					
					a = insert_query.format("apom.satresampmod07temperature",aqstime,temp3_ref,filename,resamp_path)
									
					cur.execute(a)
					cur.execute('COMMIT')
					b=update_query.format("apom.satresampmod07temperature",temp6_ref,temp10_ref,filename)
					cur.execute(b)
					cur.execute('COMMIT')
					
					
					press3_script=os.popen(press_command.format("rasref3",press3_file, filename)).read()
					first_index = press3_script.find("('")
					last_index = press3_script.find("':")
					press3_ref=press3_script[first_index+2:last_index]
					
					press6_script=os.popen(press_command.format("rasref6",press6_file, filename)).read()
					first_index = press6_script.find("('")
					last_index = press6_script.find("':")
					press6_ref=press6_script[first_index+2:last_index]
					
					press10_script=os.popen(press_command.format("rasref10",press10_file, filename)).read()
					first_index = press10_script.find("('")
					last_index = press10_script.find("':")
					press10_ref=press10_script[first_index+2:last_index]
				
					
					a = insert_query.format("apom.satresampmod07pressure",aqstime,press3_ref,filename,resamp_path)
									
					cur.execute(a)
					cur.execute('COMMIT')
					b=update_query.format("apom.satresampmod07pressure",press6_ref,press10_ref,filename)
					cur.execute(b)
					cur.execute('COMMIT')
			
	

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
def insertMYD07(query):
	org_folder = "public/SatOrgMYD07"
	resample_folder= "apom/Resample/SatResampMYD07"
	temp3_format = "_T_3km.tif"
	press3_format = "_P_3km.tif"
	temp6_format = "_T_6km.tif"
	press6_format = "_P_6km.tif"
	temp10_format = "_T_10km.tif"
	press10_format = "_P_10km.tif"
	
	
	temp_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmyd07temperature"
	press_command = "raster2pgsql -a -f rasref -F {1} apom.satresampmyd07pressure "
	
	
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			filename = row["filename"].strip()
			aqstime = row["aqstime"]
			path = row["path"].strip()
			resamp_path = path.replace(org_folder,resample_folder)
			corner = loads(row["corner"])
			
			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				path = os.path.join(constant.folder_path, path)
				
				# resampled file path
				temp3_file = os.path.join(path.replace(org_folder, resample_folder), filename + temp3_format)
				temp6_file = os.path.join(path.replace(org_folder, resample_folder), filename + temp6_format)
				temp10_file = os.path.join(path.replace(org_folder, resample_folder), filename + temp10_format)
				
				press3_file = os.path.join(path.replace(org_folder, resample_folder), filename + press3_format)
				press6_file = os.path.join(path.replace(org_folder, resample_folder), filename + press6_format)
				press10_file = os.path.join(path.replace(org_folder, resample_folder), filename + press10_format)

				# apom.satresampmod04: table name
				# -F: tu dong insert ten file thanh 1 cot
				# raster2pgsql/{1}.sql: duong dan file sql
				if os.path.isfile(temp3_file):
					temp3_script=os.popen(temp_command.format("rasref3",temp3_file, filename)).read()
					first_index = temp3_script.find("('")
					last_index = temp3_script.find("':")
					temp3_ref=temp3_script[first_index+2:last_index]
				
					temp6_script=os.popen(temp_command.format("rasref6",temp6_file, filename)).read()
					first_index = temp6_script.find("('")
					last_index = temp6_script.find("':")
					temp6_ref=temp6_script[first_index+2:last_index]
				
					temp10_script=os.popen(temp_command.format("rasref10",temp10_file, filename)).read()
					first_index = temp10_script.find("('")
					last_index = temp10_script.find("':")
					temp10_ref=temp10_script[first_index+2:last_index]
				
					a = insert_query.format("apom.satresampmyd07temperature",aqstime,temp3_ref,filename,resamp_path)
								
					cur.execute(a)
					cur.execute('COMMIT')
					b=update_query.format("apom.satresampmyd07temperature",temp6_ref,temp10_ref,filename)
					cur.execute(b)
					cur.execute('COMMIT')
				
				
					press3_script=os.popen(press_command.format("rasref3",press3_file, filename)).read()
					first_index = press3_script.find("('")
					last_index = press3_script.find("':")
					press3_ref=press3_script[first_index+2:last_index]
				
					press6_script=os.popen(press_command.format("rasref6",press6_file, filename)).read()
					first_index = press6_script.find("('")
					last_index = press6_script.find("':")
					press6_ref=press6_script[first_index+2:last_index]
				
					press10_script=os.popen(press_command.format("rasref10",press10_file, filename)).read()
					first_index = press10_script.find("('")
					last_index = press10_script.find("':")
					press10_ref=press10_script[first_index+2:last_index]
			
				
					a = insert_query.format("apom.satresampmyd07pressure",aqstime,press3_ref,filename,resamp_path)
								
					cur.execute(a)
					cur.execute('COMMIT')
					b=update_query.format("apom.satresampmyd07pressure",press6_ref,press10_ref,filename)
					cur.execute(b)
					cur.execute('COMMIT')
			
	

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
	
def insertMOD04(query):
	org_folder = "public/SatOrgMOD04"
	resample_folder= "apom/Resample/SatResampMOD04"
	aod_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampmod04 > raster2pgsql/{1}.sql"
	aod_type_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampmod04aerosoltype >> raster2pgsql/{1}.sql"
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			filename = row["filename"].strip()
			path = row["path"].strip()
			corner = loads(row["corner"])
			
			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				path = os.path.join(constant.folder_path, path)

				# resampled file path
				aod_file = os.path.join(path.replace(org_folder, resample_folder), filename + "_DT_10km.tif")
				aod_type_file = os.path.join(path.replace(org_folder, resample_folder), filename + "_AT_10km.tif")

				# apom.satresampmod04: table name
				# -F: tu dong insert ten file thanh 1 cot
				# raster2pgsql/{1}.sql: duong dan file sql
				os.system(aod_command.format(aod_file, filename))
				os.system(aod_type_command.format(aod_type_file, filename))

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
def insertMYD04(query):
	org_folder = "public/SatOrgMYD04"
	resample_folder= "apom/Resample/SatResampMYD04"
	aod_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampmyd04 >> raster2pgsql/{1}.sql"
	aod_type_command = "raster2pgsql -a -f rasref10 -F {0} apom.satresampmyd04aerosoltype >> raster2pgsql/{1}.sql"
	con = None
	try:
		con = psycopg2.connect(host=constant.host, database = constant.database, user= constant.user, password=constant.password) 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		
		#cur.execute("SELECT filename, path, ST_AsText(corner) as corner FROM satorgmod04")
		cur.execute(query)
		rows = cur.fetchall()
		
		for row in rows:
			filename = row["filename"].strip()
			path = row["path"].strip()
			corner = loads(row["corner"])
			
			if corner.within(constant.vietnam_bounds) or corner.intersects(constant.vietnam_bounds):
				path = os.path.join(constant.folder_path, path)

				# resampled file path
				aod_file = os.path.join(path.replace(org_folder, resample_folder), filename + "_CB_10km.tif")
				aod_type_file = os.path.join(path.replace(org_folder, resample_folder), filename + "_AT_10km.tif")

				# apom.satresampmod04: table name
				# -F: tu dong insert ten file thanh 1 cot
				# raster2pgsql/{1}.sql: duong dan file sql
				os.system(aod_command.format(aod_file, filename))
				os.system(aod_type_command.format(aod_type_file, filename))

	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()
def insertDEM():
	os.system("raster2pgsql -a -f rasref -F {0} apom.satresampdem > raster2pgsql/{1}.sql".format("/var/www/html/fimo/apom/Resample/SatResampDEM/dem.tif", "dem"))
	print "Done"
def insertNightLight():
	con = None
	try:
		con = psycopg2.connect(host='192.168.3.4', database='fimo', user='rasdaman', password='rasdaman') 
		cur = con.cursor(cursor_factory=psycopg2.extras.DictCursor)
		cur.execute('SELECT filename, path FROM satorgnightlight')
		rows = cur.fetchall()
		
		for row in rows:
			filename = row["filename"].strip()
			
			path = row["path"].strip()
			path = os.path.join("/var/www/html/", path)

			resfile = os.path.join(path.replace("public/SatOrgNightLight", "apom/Resample/SatResampNightLight"), filename + "_10km.tif")
			os.system("raster2pgsql -a -f rasref -F {0} apom.satresampnightlight > raster2pgsql/{1}.sql".format(resfile, filename))
	except psycopg2.DatabaseError, e:
		print 'Error %s' % e    
		sys.exit(1)
	finally:
		if con:
			con.close()

def insertLandcover():
	pass
def execute_sql():
	WORKDIR = "/home/phamha/project/Resample/raster2pgsql/"
	os.chdir(WORKDIR)
	sqlfiles = [f for f in os.listdir(".") if os.path.isfile(f) and f.endswith('.sql')]
	for f in sqlfiles:
		os.system("psql -q -d " + "\"dbname=fimo user=rasdaman password=rasdaman host=192.168.3.4 port=5432\"" + " -f " + f )
		os.chdir("../")
	print ("done")
if __name__ == '__main__':
	insertMOD04(mod04_query)
	#insertMYD04(myd04_query)
# 	insertMYD07(myd07_query)
# 	print "MYD07 done"
# 	insertMOD07(mod07_query)
# 	print "MOD07 done"
	
	
	#insertMYD07()

	#execute_sql()
